Private Sub btnTwoTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTwoTables.Click
' NOTE: to simplify the code and make it as generic as possible, we assume that
' the first column in the child table is its foreign key.
#If OLEDBBIBLIO Then
Dim pubSql As String = "SELECT PubId, Name, City FROM Publishers"
Dim titSql As String = "SELECT PubId, ISBN, Title, [Year Published] FROM Titles"
Dim joinSql As String = "SELECT Publishers.PubId, Name, City, ISBN, Title, [Year Published] FROM Publishers INNER JOIN Titles ON Publishers.PubId=Titles.PubId WHERE State='MA' AND [Year Published] > 1991 ORDER BY Publishers.PubID"
Dim cn As New OleDbConnection(BiblioConnString)
Dim pubDa As New OleDbDataAdapter(pubSql, cn)
Dim titDa As New OleDbDataAdapter(titSql, cn)
Dim joinDa As New OleDbDataAdapter(joinSql, cn)
#ElseIf OLEDBPUBS Then
Dim pubSql As String = "SELECT pub_id, pub_name, city FROM Publishers"
Dim titSql As String = "SELECT pub_id, title_id, title, pubdate FROM Titles"
Dim joinSql As String = "SELECT Publishers.pub_id, pub_name, city, title_id, title, pubdate FROM Publishers INNER JOIN Titles ON Publishers.pub_id=Titles.pub_id WHERE country='USA' AND pubdate>'10/1/1991' ORDER BY Publishers.pub_id"
Dim cn As New OleDbConnection(OledbPubsConnString)
Dim pubDa As New OleDbDataAdapter(pubSql, cn)
Dim titDa As New OleDbDataAdapter(titSql, cn)
Dim joinDa As New OleDbDataAdapter(joinSql, cn)
#ElseIf SQLPUBS Then
Dim pubSql As String = "SELECT pub_id, pub_name, city FROM Publishers"
Dim titSql As String = "SELECT pub_id, title_id, title, pubdate FROM Titles"
Dim joinSql As String = "SELECT Publishers.pub_id, pub_name, city, title_id, title, pubdate FROM Publishers INNER JOIN Titles ON Publishers.pub_id=Titles.pub_id WHERE country='USA' AND pubdate>'10/1/1991' ORDER BY Publishers.pub_id"
Dim cn As New SqlConnection(SqlPubsConnString)
Dim pubDa As New SqlDataAdapter(pubSql, cn)
Dim titDa As New SqlDataAdapter(titSql, cn)
Dim joinDa As New SqlDataAdapter(joinSql, cn)
#End If
' Open the connection.
cn.Open()
' Create the master and the detail table.
Dim pubDt As DataTable = ds.Tables.Add("Publishers")
Dim titDt As DataTable = ds.Tables.Add("Titles")
' Execute the JOIN operation into on the detail DataTable.
' (it will create a table with additional columns from the master table)
joinDa.Fill(titDt)
' Fill the schema of the master table.
pubDa.FillSchema(pubDt, SchemaType.Mapped)
' This variable holds the last value found in the master table.
Dim keyValue As String
' Extract unique foreign key values from child table.
Dim i As Integer
Dim dr As DataRow
For Each dr In titDt.Rows
' If we haven't seen this key value, create a new record in the master table.
If dr(0).ToString <> keyValue Then
' Remember the new key value.
keyValue = dr(0).ToString
' Add a new record.
Dim pubRow As DataRow = pubDt.NewRow
' Copy only the fields that belong to the master table.
For i = 0 To pubDt.Columns.Count - 1
pubRow(i) = dr(i)
Next
pubDt.Rows.Add(pubRow)
End If
Next
' removing columns belonging to the master table, but leave the foreign key
' (it is the 0-th column)
For i = pubDt.Columns.Count - 1 To 1 Step -1
titDt.Columns.RemoveAt(i)
Next
' Now we can fill the schema of the child table and close the connectin.
titDa.FillSchema(titDt, SchemaType.Mapped)
cn.Close()
' Add the relation manually.
' (Note that this statement is based on the assumption that foreign key is the
